﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MINERP.COM;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace MINERP.DAL.OverOrder
{
    public class OrverOrderDAL : DALBase
    {
        public List<orderInfo> GetOrdersByStatus(OrderInfoStatus status)
        {
            List<orderInfo> list = new List<orderInfo>();
            //foreach (var item in DataEntites.orderInfo.Where(p => p.status == (int)status))
            foreach (var item in DataEntites.orderInfo.Where(string.Format("it.status={0}", (int)status)))
            {
                list.Add(item);
            }
            return list;
        }

        public List<OutProcessRecord> GetAllOutRecordByOutShopId(int outShopId)
        {
            List<OutProcessRecord> list = new List<OutProcessRecord>();
           // OutProcessShop outShop = DataEntites.OutProcessShop.First(p => p.OutShopId == outShopId);
            //outShop.OutProcessRecord.Load();
            int status1 = (int)OutProcessRecordStatus.OutWaitFenDui;
            int status2 = (int)OutProcessRecordStatus.InGrid;
            List<OutProcessRecord> outList = DataEntites.OutProcessRecord.Where(p => p.OutProcessShop.OutShopId == outShopId && (p.Status == status1 || p.Status == status2)).ToList();
            //foreach (var item in outShop.OutProcessRecord.Where(op => op.Status != (int)OutProcessRecordStatus.Fetched && op.Status != (int)OutProcessRecordStatus.Outing))
            //foreach (var item in outShop.OutProcessRecord.Where(op => op.Status == (int)OutProcessRecordStatus.OutWaitFenDui || op.Status == (int)OutProcessRecordStatus.InGrid))
            //{
            //    list.Add(item);
            //}

            return outList;
        }
        public List<OutProcessRecord> GetAllOutRecordByOutShopIdInGrid(int outShopId)
        {
            List<OutProcessRecord> list = new List<OutProcessRecord>();
            // OutProcessShop outShop = DataEntites.OutProcessShop.First(p => p.OutShopId == outShopId);
            //outShop.OutProcessRecord.Load();
            //int status1 = (int)OutProcessRecordStatus.OutWaitFenDui;
            int status2 = (int)OutProcessRecordStatus.InGrid;
            List<OutProcessRecord> outList = DataEntites.OutProcessRecord.Where(p => p.OutProcessShop.OutShopId == outShopId && p.Status == status2).ToList();
            //foreach (var item in outShop.OutProcessRecord.Where(op => op.Status != (int)OutProcessRecordStatus.Fetched && op.Status != (int)OutProcessRecordStatus.Outing))
            //foreach (var item in outShop.OutProcessRecord.Where(op => op.Status == (int)OutProcessRecordStatus.OutWaitFenDui || op.Status == (int)OutProcessRecordStatus.InGrid))
            //{
            //    list.Add(item);
            //}

            return outList;
        }

        public void SaveChangs()
        {
            DALBase.Save();
        }

        /// <summary>
        /// 得到某节点并且某节点的工作状态为Useable的定单信息
        /// </summary>
        /// <param name="techId"></param>
        /// <returns></returns>
        public List<orderInfo> GetOrdersByCurrTechId(int techId, OrderInfoStatus status,newSampleYPDBEntities db)
        {
            List<orderInfo> listOrderInfo = new List<orderInfo>();
           // DataEntites.Refresh(System.Data.Objects.RefreshMode.StoreWins, DataEntites.WorkOrderFlowList);
           // List<WorkOrderFlowList> listWFL = DataEntites.WorkOrderFlowList.Where(wfl => wfl.tb_Tech.TechId == techId && wfl.Status == (int)OrderFlowListStatus.Useable).ToList();
            List<WorkOrderFlowList> listWFL = db.WorkOrderFlowList.Where(wfl => wfl.tb_Tech.TechId == techId && wfl.Status == (int)OrderFlowListStatus.Useable && wfl.orderInfo.OutProcessRecord.Count < 1).ToList();
            
            
            foreach (var item in listWFL)
            {
                if (!item.orderInfoReference.IsLoaded)
                {
                    item.orderInfoReference.Load();
                }
                if (!item.orderInfo.OutProcessRecord.IsLoaded)
                {
                    item.orderInfo.OutProcessRecord.Load();
                }
                listOrderInfo.Add(item.orderInfo);
                //if (status == OrderInfoStatus.FenPianCompleted)//得到将要分配委外商的
                //{
                //    if (item.orderInfo.OutProcessRecord.Count < 1)
                //    {
                //        listOrderInfo.Add(item.orderInfo);
                //    }
                //}
                //else//查看已委外,还未取件的历史记录
                //{
                //    if (item.orderInfo.OutProcessRecord.Where(opr => opr.Status != (int)OutProcessRecordStatus.Fetched).Count() > 0)
                //    {
                //        listOrderInfo.Add(item.orderInfo);
                //    }
                //}

                //if (item.orderInfo.OutProcessRecord.Count < 1)
                //{
                //    listOrderInfo.Add(item.orderInfo);
                //}
                //else if (item.orderInfo.OutProcessRecord.Where(opr => opr.Status != (int)OutProcessRecordStatus.Fetched).Count() > 0)
                //{
                //    listOrderInfo.Add(item.orderInfo);
                //}
            }

            return listOrderInfo;
        }
        /// <summary>
        /// 取得委外中的记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetOuttingTask()
        {

            string strSQL = "select  BagCode,OrderProductCode,OrderProductName,ShopName,CtmName,                                                                       " +
                            "Priority,ProductNum,                                                                                                                      " +
                            "OutShopName,CreateTime,OutRemark,PreFetchTime,FetchTime,                                                                                  " +
                            "FetchRemark,OrderBagId,                                                                                                                   " +
                            "BagName,OrderProductId,ProductCode,ProductName,UserName,Status from                                                                       " +
                            "                                                                                                                                          " +
                            "(select BagCode,OrderProductCode,OrderProductName,shopName as ShopName,ctmName as CtmName,Tb_OrderBag.FetchTime ,                                               " +
                            "orderInfo.Priority,ProductNum,                                                                                                            " +
                            "OutShopName,CreateTime,OutRemark,PreFetchTime,                                                                 " +
                            "case OutProcessRecord.Status when 0 then '外加工中' when 1 then '已收件' when 2 then  '等待分堆' when 3 then '在格子中'  end as Status,   " +
                            "FetchRemark,Tb_OrderBag.OrderBagId,                                                                                                       " +
                            "BagName,orderInfo.OrderProductId,OrderProductCode as ProductCode,ProductName,CreateUserId                                                 " +
                            "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product                                        " +
                            "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                                                            " +
                            "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                                                                   " +
                            "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                                                           " +
                            "and orderInfo.ProductId=Tb_Product.ProuctId                                                                                               " +
                            "and Shop.ShopId=Tb_OrderBag.ShopId                                                                                                        " +
                            "and OutProcessRecord.Status!=" + (int)OutProcessRecordStatus.Fetched+ "                                                                                                            " +
                            "and orderInfo.Status=" + (int)OrderInfoStatus.Doing+ ")as ta left join dbo.users on ta.CreateUserId=dbo.users.userID                                                      ";
            return ExceSQL(strSQL);

           
       }
        /// <summary>
        /// 取得快速查询的产品code
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrderProductCode()
        {

                string strSQL = "select OrderProductCode                                                                           "+
                                "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product "+  
                                "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    "+
                                "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           "+
                                "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   "+
                                "and orderInfo.ProductId=Tb_Product.ProuctId                                                       "+
                                "and Shop.ShopId=Tb_OrderBag.ShopId                                                                "+
                                "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                     " +
                                "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "   order by   OrderProductCode desc                                                                         ";
                return ExceSQL(strSQL);

        }
        /// <summary>
        /// 取得产品名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetProductName()
        {

            string strSQL = "select  distinct  ProductName                                                                           " +
                                "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    " +
                                "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           " +
                                "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   " +
                                "and orderInfo.ProductId=Tb_Product.ProuctId                                                       " +
                                "and Shop.ShopId=Tb_OrderBag.ShopId                                                                " +
                                "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                    " +
                                "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                        order by   ProductName desc                                                    ";

                return ExceSQL(strSQL);
      
        }
        /// <summary>
        /// 取得委外人
        /// </summary>
        /// <returns></returns>
        public DataTable GetCreateUser()
        {


            string strSQL = "select  distinct  UserName from                                                                      " +
                                "(select         " +
                                "CreateUserId          " +
                                "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                     " +
                                "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                            " +
                                "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                    " +
                                "and orderInfo.ProductId=Tb_Product.ProuctId                                                        " +
                                "and Shop.ShopId=Tb_OrderBag.ShopId                                                                 " +
                                "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                     " +
                                "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + ")as ta left join dbo.users on ta.CreateUserId=dbo.users.userID          ";
                return ExceSQL(strSQL);
            
        }
        /// <summary>
        /// 取得商户名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetShop()
        {

            string strSQL = "select  distinct Shop.ShopId,shopName as ShopName                                                                       " +
                                "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    " +
                                "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           " +
                                "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   " +
                                "and orderInfo.ProductId=Tb_Product.ProuctId                                                       " +
                                "and Shop.ShopId=Tb_OrderBag.ShopId                                                                " +
                                "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                    " +
                                "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                              order by   shopName asc                                       ";
                return ExceSQL(strSQL);

        }
        /// <summary>
        /// 取得客户列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetCtmName(int  inShopId)
        {
            string strSQL="";
            if (inShopId != 0)
            {

                strSQL = "select  distinct CtmName                                                                      " +
                                 "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                 "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    " +
                                 "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           " +
                                 "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   " +
                                 "and orderInfo.ProductId=Tb_Product.ProuctId                                                       " +
                                 "and Shop.ShopId=Tb_OrderBag.ShopId                                                                " +
                                 "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                     " +
                                 "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                                                                            " +
                                 "and Shop.ShopId= " + inShopId + "   order by   CtmName desc";
            }
            else
            {
                strSQL = "select  distinct CtmName                                                                      " +
                                               "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                               "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    " +
                                               "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           " +
                                               "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   " +
                                               "and orderInfo.ProductId=Tb_Product.ProuctId                                                       " +
                                               "and Shop.ShopId=Tb_OrderBag.ShopId                                                                " +
                                               "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                     " +
                                               "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                                                                            " +
                                               " union  select ('全部')  order by   CtmName desc";
            }
                return ExceSQL(strSQL);


               
        }
        /// <summary>
        /// 取得委外商户名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetOutShop()
        {


            string strSQL = "   select  distinct OutShopName                                                                      " +
                                "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                                "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                    " +
                                "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                           " +
                                "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                   " +
                                "and orderInfo.ProductId=Tb_Product.ProuctId                                                       " +
                                "and Shop.ShopId=Tb_OrderBag.ShopId                                                                " +
                                "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                     " +
                                "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                                                                          ";
                return ExceSQL(strSQL);

      
        }

       
        /// <summary>
        /// 取得委外商户名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetPriority()
        {


            string strSQL ="select  distinct convert (varchar(10),orderInfo.Priority)  as  Priority                             "+                 
                            "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product  "+
                            "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                      "+
                            " and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                            "+
                            " and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                    "+
                            " and orderInfo.ProductId=Tb_Product.ProuctId                                                        "+
                            "and Shop.ShopId=Tb_OrderBag.ShopId                                                                  "+
                            "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                       " +
                            "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + "                                                                              " +
                            "                                                    ";                                                
            return ExceSQL(strSQL);


        }
        /// <summary>
        /// 取得委外中的记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetOuttingTaskByBarCode(string inBarCode)
        {

            string strSQL = "select  BagCode,OrderProductCode,OrderProductName,ShopName,CtmName,                                " +
                            "Priority,ProductNum,                                                                               " +
                            "OutShopName,CreateTime,OutRemark,PreFetchTime,FetchTime,                                           " +
                            "FetchRemark,OrderBagId,                                                                            " +
                            "BagName,OrderProductId,ProductCode,ProductName,UserName from                                       " +
                            "                                                                                                   " +
                            "(select BagCode,OrderProductCode,OrderProductName,shopName as ShopName,ctmName as CtmName,         " +
                            "orderInfo.Priority,ProductNum,                                                                     " +
                            "OutShopName,CreateTime,OutRemark,PreFetchTime,OutProcessRecord.FetchTime,                          " +
                            "FetchRemark,Tb_OrderBag.OrderBagId,                                                                " +
                            "BagName,orderInfo.OrderProductId,OrderProductCode as ProductCode,ProductName,CreateUserId          " +
                            "from dbo.OutProcessRecord,dbo.OutProcessShop,dbo.orderInfo,dbo.Tb_OrderBag,dbo.Shop,dbo.Tb_Product " +
                            "where OutProcessRecord.OrderProductId=orderInfo.OrderProductId                                     " +
                            "and OutProcessRecord.OutShopId=OutProcessShop.OutShopId                                            " +
                            "and orderInfo.OrderBagId=Tb_OrderBag.OrderBagId                                                    " +
                            "and orderInfo.ProductId=Tb_Product.ProuctId                                                        " +
                            "and Shop.ShopId=Tb_OrderBag.ShopId                                                                 " +
                            "and OutProcessRecord.Status=" + (int)OutProcessRecordStatus.Outing + "                                                                      " +
                            "and orderInfo.OrderProductCode= '" +inBarCode+"' "+
                            "and orderInfo.Status=" + (int)OrderInfoStatus.Doing + ")as ta left join dbo.users on ta.CreateUserId=dbo.users.userID               ";
            return ExceSQL(strSQL);

           
        }
        /// <summary>
        /// 取得所有委外商的未完成列表
        /// </summary>
        /// <param name="inOutShopId"></param>
        /// <returns></returns>
        public DataSet GetAllUnComplateProduct(int inOutShopId)
        {
            string strSQL = "select Shop.shopName,orderInfo.CtmName,OrderProductName,productNum,ProductName,                                                   " +
                            "OutProcessRecord.CreateTime,PreFetchTime,count(photoId) as photoNum,OutShopName,                                                  " +
                            "OutProcessShop.Tel1 as tel,OutProcessShop.Addr,OutProcessShop.Keeper,OrderProductCode,                                            " +
                            "Convert(varchar(10),isnull(Tb_Product.Width,0)) +'×'+Convert(varchar(10),isnull(Tb_Product.Height,0))                            " +
                            "+'('+Convert(varchar(10),isnull(Tb_Product.Width,0)*2.54)+'×'+Convert(varchar(10),isnull(Tb_Product.Height,0)*2.54 )+')' as spec," +
                            "dbo.OutProcessRecord.Remark                                                                                                       " +
                            "from orderInfo,dbo.OutProcessRecord,dbo.WorkOrderFlowList,dbo.Shop,dbo.Tb_OrderBag,dbo.photoInfo,Tb_Product,OutProcessShop        " +
                            "where orderInfo.OrderProductId=OutProcessRecord.OrderProductId                                                                    " +
                            "and orderInfo.OrderProductId=WorkOrderFlowList.OrderProductId                                                                     " +
                            "and orderInfo.OrderProductId=photoInfo.OrderProductId                                                                             " +
                            "and Tb_OrderBag.OrderBagId=orderInfo.OrderBagId                                                                                   " +
                            "and orderInfo.ProductId=Tb_Product.ProuctId                                                                                       " +
                            "and OutProcessShop.OutShopId= OutProcessRecord.OutShopId                                                                          " +
                            "and orderInfo.status=" + (int)OrderInfoStatus.Doing + "                                                                                                            " +
                            "and WorkOrderFlowList.TechId=9                                                                                                    " +
                            "and WorkOrderFlowList.Status=" + (int)OrderFlowListStatus.Useable + "                                                                                                    " +
                            "and Shop.ShopId=Tb_OrderBag.shopID                                                                                                " +
                            "and OutProcessRecord.OutShopId="+inOutShopId    +
                            "group by Shop.shopName,orderInfo.CtmName,OrderProductName,                                                                        " +
                            "ProductName,productNum,dbo.OutProcessRecord.CreateTime,PreFetchTime,Tb_Product.Width,                                             " +
                            "tb_Product.Height,OutShopName,OutProcessShop.Tel1,                                                                                " +
                            "OutProcessShop.Addr,OutProcessShop.Keeper,OrderProductCode,OutProcessRecord.Remark                                                ";
            
            return ExceSQLDataSet(strSQL);
        }
        public OutProcessShop GetOutShop(int inOutShopId)
        {
            return DataEntites.OutProcessShop.Where(p => p.OutShopId == inOutShopId).FirstOrDefault();
        }
    }
}
